package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import util.Util;
import model.Student;

public class StuDao {
	public List <Student> findAll(){
		List <Student> stus = new ArrayList<Student>();
		Connection con = null;
		try {
			con = Util.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from db_student_p");
			while(rs.next()){
				Student stu = new Student(rs.getInt("id"),rs.getString("name"),rs.getInt("age"),rs.getString("sex"));
				stus.add(stu);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return stus;
		}
		finally{
			try {
				Util.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return stus;
	}
	public List <Student> findStuByName(String name){
		List <Student> stus = new ArrayList<Student>();
		Connection con = null;
		try {
			con = Util.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from db_student_p where name like '%"+name+"%'");
			while(rs.next()){
				Student stu = new Student(rs.getInt("id"),rs.getString("name"),rs.getInt("age"),rs.getString("sex"));
				stus.add(stu);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return stus;
		}
		finally{
			try {
				Util.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return stus;
	}
	public List <Student> findStuById(int id){
		List <Student> stus = new ArrayList<Student>();
		Connection con = null;
		try {
			con = Util.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from db_student_p where id ="+id);
			while(rs.next()){
				Student stu = new Student(rs.getInt("id"),rs.getString("name"),rs.getInt("age"),rs.getString("sex"));
				stus.add(stu);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return stus;
		}
		finally{
			try {
				Util.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return stus;
	}
	public boolean insertStu(Student stu){
		Connection con = null;
		try {
			con = Util.getConnection();
			String sql = "insert into db_student_p values (?,?,?,?)";
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1, stu.getId());	
			pst.setString(2, stu.getName());
			pst.setInt(3, stu.getAge());
			pst.setString(4, stu.getSex());
			pst.execute();
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return false;
		}
		finally{
			try {
				Util.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public boolean deleteStuById(int id){
		Connection con = null;
		try {
			con = Util.getConnection();
			String sql = "delete from db_student_p where id=?";
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1, id);	
			pst.execute();
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return false;
		}
		finally{
			try {
				Util.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public boolean updateStu(Student stu){
		Connection con = null;
		try {
			con = Util.getConnection();
			String sql = "update db_student_p set name=?,age=?,sex=? where id=?";
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(4, stu.getId());	
			pst.setString(1, stu.getName());
			pst.setInt(2, stu.getAge());
			pst.setString(3, stu.getSex());
			pst.execute();
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return false;
		}
		finally{
			try {
				Util.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
